#========================================  Data Sources  =========================================#
Data Sources:
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- ConnectionString: 'airport_budget_final.xlsx'
  Provider: 'Excel@2'
  Name: 'Budget'
  Technology: 'Excel'
  Sheets: 'Budget, SPG_OPS_RUNWAY, SPG_IT_SYSUP, SPG_SEC_TRAIN, SPG_RTL_TENANT, SPC_LOG_FLEET, SPC_HR_RECRUIT, SPS_CS_TRPROG, SPS_FAC_CLEAN, SPG_FIN_AUDIT, SPG_MKT_ADVERT'
  Normalize Column Names: 'True'
  Normalize Table Names: 'True'
#============================================  Tests  ============================================#
Tests:
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- Suite: 'Uniqueness'
  Order: '0'
  Name: 'No duplicities on Budget sheet'
  Description:  |
    When this test fails, there is a duplicity in combination of these columns:
    Subsidiary, Department, Budget Category
    Combination of these columns must be unique.
  Expectation: 'Set is Empty'
  FirstDataSource: 'Budget'
  FirstQuery:  |
    select subsidiary, department, budget_category
    from budget.budget
    group by subsidiary, department, budget_category
    having count(*) > 1
  MaximumErrorsLogged: '10'
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- Suite: 'Completness'
  Order: '0'
  Name: 'Expected sheet name must always be filled.'
  Description: 'Every row on main budget sheet must have expected sheet name filled. Consistency checks are mainly generated and they use this field.'
  Expectation: 'Set is Empty'
  FirstDataSource: 'Budget'
  FirstQuery:  |
    select  *
    from    budget.budget
    where   expected_sheet_name is null
            or trim(expected_sheet_name) = ''
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- Suite: 'Consistency'
  Order: '0'
  Name: '%budget_category% %department% %subsidiary%  consistency check'
  Description: 'The sum of items on %expected_sheet_name% sheet must match the total on the main Budget sheet.'
  Expectation: 'SetsMatch'
  FirstDataSource: 'Budget'
  FirstQuery:  |
    select budget_amount as BudgetAmountFromMainSheet
    from  budget.budget
    where expected_sheet_name = '%expected_sheet_name%'

  SecondDataSource: 'Budget'
  SecondQuery:  |
    select sum(budget_amount) as BudgetAmountFromDetails
    from budget.%expected_sheet_name%

  Metadata: 'Main budget table'
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- Suite: 'Validity'
  Order: '0'
  Name: 'No invalid items for %budget_category% (%department%)'
  Expectation: 'Set is Empty'
  FirstDataSource: 'Budget'
  FirstQuery:  |
    select  *
    from budget.%expected_sheet_name%
    where budget_amount <= 0 or budget_amount is null
            or currency <> 'CZK'

  Metadata: 'Main budget table'
  Description: 'All budget items must be in CZK, with filled amount greater than 0.'
  MaximumErrorsLogged: '20'
#===========================================  Queries  ===========================================#
Queries:
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- Name: 'Query 1'
  Query Text:  |
    select * from information_schema.tables
    -- select budget_amount from Budget."2025_budget" where department = 'IT' and budget_category = 'System Upgrades';
    --select sum(budget_amount) from Budget.skyport_group_it_system_upgrade

    -- select * from Budget.budget where expected_sheet_name = 'SPG_IT_SYSUP'
    --select * from Budget.spg_it_sysup where budget_amount <= 0 or budget_amount is null

  Data Source: 'Budget'
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- Name: 'Main budget table'
  Query Text:  |
    select  *
    from    budget.budget

  Data Source: 'Budget'
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - #
- Name: 'Query 2'
  Query Text:  |
    select * from  Budget.spg_it_sysup
    -- select budget_amount from Budget."2025_budget" where department = 'IT' and budget_category = 'System Upgrades';
    --select sum(budget_amount) from Budget.skyport_group_it_system_upgrade

    -- select * from Budget.budget where expected_sheet_name = 'SPG_IT_SYSUP'
    --select * from Budget.spg_it_sysup where budget_amount <= 0 or budget_amount is null

  Data Source: 'Budget'
#=================================================================================================#
